OpenEdge Development:<br />ProDataSets Retrieving detail for the ProDataSet

Now you want to get the OrderLines for a selected Order. You can do this by responding to a double-click on an Order in the browse.

Define a trigger block ON MOUSE-SELECT-DBLCLICK of OrderBrowse (this is one of the Portable Mouse Events).

When the user selects an Order, the trigger saves off the Order number for later reference. It must then delete the temp-table row for that Order. For example:


DO: DEFINE VARIABLE i`Order`Num AS INTEGER NO-UNDO. i`Order`Num = `ttOrder`.`Order`Num. CLOSE QUERY `Order`Browse. DELETE `ttOrder`.

Why is this? When you pass the request to the support procedure to fill in the detail, it is going to prepare the top-level Order query for that one selected Order, set the FILL-MODE back to “APPEND” for OrderLines and Items, and then do a FILL. That FILL will read the Order into the top-level table along with its OrderLines and all Items. When this is returned to the client to be appended to the data already in the client’s ProDataSet, that ttOrder row is going to be a duplicate of the row already there, and this will cause an error. Remember, when you use the OUTPUT APPEND parameter form, new data in each temp-table is always appended to the rows already there. There is no ability to use the FILL-MODE to define a different behavior for each temp-table.

Therefore, you must delete rows that will result in duplicates in advance, either on the sending side or the receiving side, before the new data is appended. In this case, deleting the existing row has the advantage of returning the latest field values for the Order along with its OrderLines, giving you what amounts to a REFRESH mode for the Order. This, in general, is how you accomplish a refresh of data in a ProDataSet, simply by deleting the rows you want to refresh and then requesting them again.

In case the user has selected an Order that has been selected before, you delete any OrderLines for it so that they are refreshed as well. For example:


FOR EACH `ttOline` WHERE `ttOline`.`Order`Num = i`Order`Num: DELETE `ttOline`. END.

You run another support procedure in orderSupport.p, called fetchOrderDetail, passing in the selected Order Number and a flag telling the procedure whether Items have already been returned or not. If the Items are already on the client they do not have to be refilled and passed from the server. The ProDataSet is received in APPEND mode so that the Order and OrderLines that come back are added to what’s already in the local ProDataSet, as shown:


`RUN` fetch`Order`Detail IN h`Order`Proc (`INPUT` i`Order`Num, `INPUT` NOT CAN-FIND (FIRST `ttItem`), `OUTPUT` DATASET `dsOrder` `APPEND`).

To reset the user interface, you need to open the top-level query, reposition the query to the selected Order (which repositions the browse as well), and do a SYNCHRONIZE to reset the dependent queries for OrderLines and Items as well. For example:

OPEN QUERY `Order`Browse FOR EACH `ttOrder`. FIND `ttOrder` WHERE `ttOrder`.`Order`Num = i`Order`Num. REPOSITION `Order`Browse TO ROWID ROWID(`ttOrder`). DATASET `dsOrder`:GET-BUFFER-HANDLE(1):`SYNCHRONIZE`(). END.

OPEN QUERY OrderBrowse FOR EACH ttOrder. 
    FIND ttOrder WHERE ttOrder.OrderNum = iOrderNum. 
    REPOSITION OrderBrowse TO ROWID ROWID(ttOrder). 
    DATASET dsOrder:GET-BUFFER-HANDLE(1):SYNCHRONIZE(). 
END.

In OrderSupport.p, the fetchOrderDetail procedure empties the server-side ProDataSet and resets the selection to fill just the one selected Order. It resets the FILL-MODE for the ttOline table from “NO-FILL” to “APPEND” so that OrderLines are read for the Order, and sets the FILL-MODE for the ttItem table so that Items are filled the first time an Order is selected, and then left out of the fill after that. For example:

PROCEDURE fetch`Order`Detail: DEFINE `INPUT` PARAMETER pi`Order`Num AS INTEGER NO-UNDO. DEFINE `INPUT` PARAMETER lFill`Items` AS LOGICAL NO-UNDO. DEFINE `OUTPUT` PARAMETER DATASET FOR `dsOrder` `BY-VALUE`. hDataSet:EMPTY-DATASET. cSelection = "`Order`Num = " + STRING(pi`Order`Num). hDataSet:GET-BUFFER-HANDLE(2):`FILL-MODE` = "`APPEND`". /* `ttOline` / hDataSet:GET-BUFFER-HANDLE(3):`FILL-MODE` = / `ttItem` */ IF lFill`Items` THEN "`APPEND`" ELSE "NO-`FILL`". hDataSet:`FILL`(). END PROCEDURE.

PROCEDURE fetchOrderDetail: 
     DEFINE INPUT  PARAMETER piOrderNum  AS INTEGER    NO-UNDO. 
     DEFINE INPUT  PARAMETER lFillItems  AS LOGICAL    NO-UNDO. 
     DEFINE OUTPUT PARAMETER DATASET FOR dsOrder BY-VALUE. 
     hDataSet:EMPTY-DATASET. 
     cSelection = "OrderNum = " + STRING(piOrderNum). 
     hDataSet:GET-BUFFER-HANDLE(2):FILL-MODE = "APPEND".  /* ttOline */ 
     hDataSet:GET-BUFFER-HANDLE(3):FILL-MODE =            /* ttItem  */ 
         IF lFillItems THEN "APPEND" ELSE "NO-FILL". 
     hDataSet:FILL(). 
END PROCEDURE.

With all this done, you can fully test the procedures.

First make sure that your dsOrder.i include file has the REPOSITION keyword for the LineItem relation so that you can see all the items in the ttItem browse.

Run PickOrder.w, select a set of Orders using the filter fields, and then double-click on one of the Orders, as shown:

The first time you double-click on an Order, its OrderLines and all Items are brought over from OrderSupport.p. After that, double-clicking on another Order brings over its OrderLines without refetching the Items. If you simply select an Order without double-clicking on it, this empties the ttOline browse, showing that its OrderLines are not yet part of the client’s ProDataSet.